-- 检查存储过程是否存在，存在则删除
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'InventoryDeduction')
BEGIN
    DROP PROCEDURE InventoryDeduction;
END
GO
-- 创建存储过程
CREATE PROCEDURE InventoryDeduction
    @NutBatchCode NVARCHAR(1000),--螺母批次码
	@DustCoverBatchCode NVARCHAR(1000),--防尘盖批次码
	@ComponentBatchCode NVARCHAR(1000),--合件批次码
	@OutPutResult BIT OUTPUT --返回结果
AS
BEGIN
BEGIN TRANSACTION AS_TRANSACTION

DECLARE @TotalAffectedRows INT = 0; --统计受影响行数
--PRINT CAST(@NutBatchCode AS NVARCHAR);
--PRINT CAST(@DustCoverBatchCode AS NVARCHAR);
--PRINT CAST(@ComponentBatchCode AS NVARCHAR);

--MaterialType 物料类型 1：螺母-Nut   2：防尘盖-DustCover  3：合件-Component
WITH FIFO_Inventory_Nut AS (
    SELECT top 1 *
    FROM [dbo].[Inventory]
    WHERE SurplusQty > 0 AND MaterialType = 1
      AND MainBatchCode = @NutBatchCode
    ORDER BY CreateTime ASC   -- 获取符合条件的记录并按 CreateTime 排序，确保先进先出
)
UPDATE FIFO_Inventory_Nut SET SurplusQty = SurplusQty - SingleDeductionQty WHERE SurplusQty > 0 
SET @TotalAffectedRows = @TotalAffectedRows + @@ROWCOUNT;--统计受影响行数记录


WITH FIFO_Inventory_DustCover AS (
    SELECT top 1 *
    FROM [dbo].[Inventory]
    WHERE SurplusQty > 0 AND MaterialType = 2
      AND MainBatchCode = @DustCoverBatchCode
    ORDER BY CreateTime ASC   -- 获取符合条件的记录并按 CreateTime 排序，确保先进先出
)
UPDATE FIFO_Inventory_DustCover SET  SurplusQty = SurplusQty - SingleDeductionQty WHERE SurplusQty > 0 
SET @TotalAffectedRows = @TotalAffectedRows + @@ROWCOUNT;--统计受影响行数记录


WITH FIFO_Inventory_Component AS (
    SELECT top 1 *
    FROM [dbo].[Inventory]
    WHERE SurplusQty > 0 AND MaterialType = 3
      AND MainBatchCode = @ComponentBatchCode
    ORDER BY CreateTime ASC  -- 获取符合条件的记录并按 CreateTime 排序，确保先进先出
)
UPDATE FIFO_Inventory_Component SET  SurplusQty = SurplusQty - SingleDeductionQty WHERE SurplusQty > 0 
SET @TotalAffectedRows = @TotalAffectedRows + @@ROWCOUNT;--统计受影响行数记录


IF @TotalAffectedRows = 3 
	BEGIN
		COMMIT;--扣减成功
		SELECT 1 AS Result;
		SET @OutPutResult = 1;
	END
ELSE 
	BEGIN
		ROLLBACK;--扣减失败，则全部回滚
		SELECT 0 Result;
		SET @OutPutResult = 0;
	END
END

